Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

Results retrieved from a database

This section identifies and briefly defines three coding techniques that Progress 4GL supports to retrieve data source results. In general, you can think of these techniques as handling complex query results for which each technique has its own purpose and benefits.

Using the Progress-supplied proc-text-buffer for row results

This technique allows you to:

Access result sets available through a pseudo table for which each row is a long character string.
Requires you to parse each long string to access specific fields.

The Proc-text-buffer is part of the schema holder, making this result set option independent of the foreign data source.

Defining a special view on the ORACLE data source to use as a buffer for row results

This technique allows you to:

Define views within the foreign data source.
Use the result set buffer configuration of the view to access the data returned by the stored procedure.

This technique involves some administration on the foreign datasource and therefore is considered a result set option that is dependent on the foreign data source.

Loading results into a temp-table

This technique allows you to:

Specify one or more temp-table handle elements to retrieve stored procedure result sets and have the DataServer load the result set data directly into the associated temp-table(s).
Have direct access to the fields defined in the temp-table.

The temp-table approach maintains some separation between the data and the foreign data source which allows you to process the result sets quite independent of its database origin. Also, the result set definition is not contained within the schema holder.

Unless you pass unprepared dynamic temp-table handles into which the result sets are to be loaded, you still need to ensure that the temp-table record definition matches that of the stored procedures result set(s).

Passing empty temp-tables with the LOAD-RESULT-INTO phrase generates temp-table definitions dynamically based on a default mapping of data types. This method provides a data retrieval method completely independent of the foreign data source.

The LOAD-RESULT-INTO method represents a highly efficient means to transfer stored procedure result sets into temp-tables and provides significant performance gains over other techniques used to accomplish this same goal.

Note: The proc-text-buffer and the special native views techniques use the basic RUN STORED-PROCE statement and are backward compatible to earlier versions of the Progress 4GL. The LOAD-RESULT-INTO phrase provides a new technique to populate temp-tables directly in the 4GL.